package org.example;


import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.alter.AlterExpression;
import net.sf.jsqlparser.statement.insert.Insert;

import java.io.*;
import java.util.List;

public class SQLUtils {

    private static int ARRMAXNUM = 10;

    public static void nSQL2idempotentSQL1(String checktrr) throws Exception {
        File sourceSQLFile = new File("./sourceSQL.sql");
        File targetSQLFile = new File("./targetSQL.sql");

        if (!sourceSQLFile.exists()) {
            //先得到文件的上级目录，并创建上级目录，再创建文件
            sourceSQLFile.getParentFile().mkdir();
            try {
                //创建文件
                sourceSQLFile.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (!targetSQLFile.exists()) {
            //先得到文件的上级目录，并创建上级目录，再创建文件
            targetSQLFile.getParentFile().mkdir();
            try {
                //创建文件
                targetSQLFile.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        BufferedReader br = new BufferedReader(new FileReader(sourceSQLFile));
        PrintStream ps = new PrintStream(new FileOutputStream(targetSQLFile));
        ps.println("#转化后的sql");

        String st;
        while ((st = br.readLine()) != null) {
            if (!st.endsWith(";")) {
                continue;
            }
            if (st.startsWith("I") || st.startsWith("i")){
                String targetSQL = transInsert(st,checktrr);
                ps.append(targetSQL).println();
            } else {
                String targetSQL = transAlter(st);
                ps.append(targetSQL).println();
            }
        }
    }



    /**
     * 脚本转换
     *
     * @param sourceSQL 原始脚本
     * @param checkAttr 校验属性（排重属性）
     */
    private static String transInsert(String sourceSQL, String checkAttr) throws Exception {
        //1.识别出表名
        CCJSqlParserManager ccjSqlParserManager = new CCJSqlParserManager();
        Insert insertSQL = (Insert) ccjSqlParserManager.parse(new StringReader(sourceSQL));
        //表对象
        Table table = insertSQL.getTable();
        //获取列
        List<Column> columns = insertSQL.getColumns();
        //获取项目列表值
        ExpressionList values = (ExpressionList) insertSQL.getItemsList();

        if (columns.size() != values.getExpressions().size()){
            throw new Exception("请检查sql格式！列和值不相等");
        }

        StringBuffer sbResult = new StringBuffer();
        //动态拼接， append（）方法相当于“+”，将指定的字符串追加到此字符序列。
        sbResult.append(" INSERT INTO ").append(table.getName()).append(" ( ");
        //同时这里要找到排重属性在values中的值
        int[] checkAttrIndex = new int[ARRMAXNUM];
        int index = 0;
        String[] checkAttrArr = checkAttr.split(",");
        for (int i = 0; i < columns.size(); i++) {
            for (int j = 0; j < checkAttrArr.length; j++) {
                Column column = columns.get(i);
                if (j == 0){
                    sbResult.append(column.getColumnName()).append(",");
                }
                if (("`" + checkAttrArr[j] + "`").equalsIgnoreCase(column.getColumnName())) {
                    checkAttrIndex[index] = i;
                    index++;
                    break;
                }
            }
        }

        if (index != checkAttrArr.length){
            throw new Exception("请检查sql格式！指定排重的列不存在");
        }

        sbResult = new StringBuffer(sbResult.substring(0,sbResult.length() -1));

        sbResult.append(" ) ");

        sbResult.append(" SELECT ").append(values.toString(), 1, values.toString().length() - 1);
        sbResult.append(" from DUAL where  not EXISTS( SELECT * from ")
                .append(table.getName()).append(" where ");
        for (int i = 0; i < checkAttrArr.length; i++) {
            if (i == 0){
                sbResult.append(checkAttrArr[i]).append(" = ").append(values.getExpressions().get(checkAttrIndex[i]));
            }else {
                sbResult.append(" and ").append(checkAttrArr[i]).append(" = ").append(values.getExpressions().get(checkAttrIndex[i]));
            }
        }

        sbResult.append(");");

        return sbResult.toString();
    }

    private static String transAlter(String sourceSQL) throws JSQLParserException {
        //将原始SQL语句定义为一个SQL语句的对象
        CCJSqlParserManager ccjSqlParserManager = new CCJSqlParserManager();
        Alter alterSQL = (Alter)  ccjSqlParserManager.parse(new StringReader(sourceSQL));
        //获取表名
        Table table = alterSQL.getTable();
        //获取修改表达式,
        List<AlterExpression> alterExpressions = alterSQL.getAlterExpressions();
        String s1 = alterExpressions.get(0).toString();
        //获取添加字段

        //System.out.println(alterExpressions.get(0).getColDataTypeList().get(0).getColumnName());

        //截取要添加的字段名
        /*String ss = "COLUMN";
        String s = sourceSQL;
        String s3 = StringUtils.substringBetween(s,ss+" `","`");*/

        //字符串的拼接
        StringBuffer sbResult = new StringBuffer(" drop procedure if exists ALTER_TB; ").append("\n");
        sbResult.append(" DELIMITER // ").append("\n")
                .append(" CREATE PROCEDURE ALTER_TB()" ).append("\n")
                .append(" BEGIN ").append("\n")
                .append(" IF not EXISTS(SELECT 1 ").append("\n")
                .append(" FROM INFORMATION_SCHEMA.Columns ").append("\n")
                .append(" where ")
                .append(" table_schema = DATABASE() ").append("\n")
                .append(" AND ")
                .append(" table_name = '").append(table.getName()).append("'\n")
                .append(" AND ")
                .append(" column_name = '")
                .append(alterExpressions.get(0).getColDataTypeList().get(0).getColumnName()).append("') ").append("\n")
                .append(" THEN ").append("\n")
                .append(" ALTER TABLE ").append(table.getName()).append(" ")
                .append(s1).append(";").append("\n")
                .append(" END IF; ").append("\n")
                .append(" END // ").append("\n")
                .append(" DELIMITER ; ").append("\n")
                .append(" call ALTER_TB(); ").append("\n")
                .append(" drop procedure if exists ALTER_TB; ");

        return sbResult.toString();
    }


    //public static String sourceSQL = "INSERT INTO `mcb_dicttool`.`tb_mlf_cdwh_otherdict`(`ID`, `DICTNAME`, `CONTENT`, `CATEGORY`, `DICTNUM`, `ORDERNUM`, `TYPE`, `bak1`, `bak2`, `bak0`, `ISPRODUCT`, `is_product`) VALUES ('xuexiqiangguo_009040', '环球视野', '环球视野', 'xuexiqiangguo_navigation', '009040', 1, 'dir', NULL, NULL, NULL, '0', NULL);";

    public static void main(String[] args) throws Exception {


        try {
            if ((args.length > 0)) {
                nSQL2idempotentSQL1(args[0]);
            } else {
                nSQL2idempotentSQL1("");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
